import java.awt.Point;
import java.net.InetAddress;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

public class SQLDatabase {
	public SynchQueue queue = new SynchQueue();

	public ArrayList getID(Connection connection) throws Exception {
		ArrayList idlist = new ArrayList();
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT E_ID FROM cs2.ENTITY;");
		while (rs.next()) {
			int id = rs.getInt("E_ID");
			idlist.add(new Integer(id));
		}
		return idlist;
	}

	public ArrayList EntityData() throws Exception {
		ArrayList all = new ArrayList();
		Connection connection = connect();
		// int id, int type, String name, Point location
		ArrayList idList = getID(connection);
		Iterator it = idList.listIterator();
		PlayerEntityData player;
		MonsterEntityData monster;
		while (it.hasNext()) {
			int id = ((Integer) it.next()).intValue();
			int type =  ((Integer)getClass(connection, id)).intValue();
			String name = (String) getName(connection, id);
			Point location = new Point(getLocX(connection, id), getLocY(
					connection, id));
			InetAddress ip = null;
			if (type <= 1) {
				player = new PlayerEntityData(id, type, name, location, ip);
				all.add((EntityData) player);
			} else {
				monster = new MonsterEntityData(id, type, name, location);
				all.add((EntityData) monster);
			}
		}
		return all;
	}

	public ArrayList PlayerEntityData() throws Exception {
		ArrayList all = new ArrayList();
		Connection connection = connect();
		// int id, int type, String name, Point location, InetAddress ip
		ArrayList idList = getID(connection);
		Iterator it = idList.listIterator();
		PlayerEntityData player;
		while (it.hasNext()) {
			int id = ((Integer) it.next()).intValue();
			int type =  ((Integer)getClass(connection, id)).intValue();
			String name = (String) getName(connection, id);
			Point location = new Point(getLocX(connection, id), getLocY(
					connection, id));
			InetAddress ip = null;
			player = new PlayerEntityData(id, type, name, location, ip);
			if (type <= 1)
				all.add((PlayerEntityData) player);
		}
		return all;
	}
	
	public PlayerEntityData getStoredPlayer(String name) throws Exception{
		Connection connection = connect();
		PlayerEntityData player;
		//int id, int type, String name, Point location, InetAddress ip
		int id = ((Integer) getID(connection, name)).intValue();
		int type =  ((Integer)getClass(connection, id)).intValue();
		Point location = new Point(getLocX(connection, id), getLocY(
				connection, id));
		InetAddress ip = null;
		player = new PlayerEntityData(id, type, name, location, ip);
		return player;
	}

	public ArrayList MonsterEntityData() throws Exception {
		ArrayList all = new ArrayList();
		Connection connection = connect();
		// int id, int type, String name, Point location
		ArrayList idList = getID(connection);
		Iterator it = idList.listIterator();
		MonsterEntityData monster;
		while (it.hasNext()) {
			int id = ((Integer) it.next()).intValue();
			int type =  ((Integer)getClass(connection, id)).intValue();
			String name = (String) getName(connection, id);
			Point location = new Point(getLocX(connection, id), getLocY(
					connection, id));
			monster = new MonsterEntityData(id, type, name, location);
			if (type > 1)
				all.add((MonsterEntityData) monster);
		}
		return all;

	}

	public Connection connect() throws Exception {
		String serverName = "172.16.5.1";
		String mydatabase = "cs2";
		String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a
		// JDBC
		// url
		String username = "cs2";
		String password = "cs2";
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		Connection connection = DriverManager.getConnection(url, username,
				password);
		return connection;
	}
	

	public void addName(String name, int classname, String ip) {
		String sql = "INSERT INTO cs2.ENTITY (E_NAME, E_IP, E_CLASS) "
				+ "VALUES('" + name + "','" + ip + "','" + classname + "')";
		queue.enqueue(sql);
	}

	public String getName(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_NAME FROM cs2.ENTITY where E_ID=" + id
						+ ";");
		rs.next();
		String s = rs.getString("E_NAME");
		return s;
	}
	public int getID(Connection connection, String name) throws Exception{
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_ID FROM cs2.ENTITY where E_NAME="
						+ name + ";");
		rs.next();
		int id = rs.getInt("E_ID");
		return id;
	}

	public void setAlive(int id, int alive) {
		String sql = "UPDATE cs2.ENTITY set E_ALIVE =" + alive
				+ " where E_ID = " + id + ";";
		queue.enqueue(sql);
	}

	public void setOnline(int id, int online)
	{
		String sql = "UPDATE cs2.ENTITY set E_ONLINE =" + online
				+ " where E_ID = " + id + ";";
		queue.enqueue(sql);
	}

	public int getOnline(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_ONLINE FROM cs2.ENTITY where E_ID="
						+ id + ";");
		rs.next();
		int online = rs.getInt("E_ONLINE");
		return online;
	}

	public void setLoc(int id, int x, int y)
		{
		String sql = "UPDATE cs2.ENTITY set E_LOC_X =" + x + ", E_LOC_Y =" + y
				+ " where E_ID = " + id + ";";
		queue.enqueue(sql);
	}

	public int getLocX(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_LOC_X FROM cs2.ENTITY where E_ID=" + id
						+ ";");
		rs.next();
		int location = rs.getInt("E_LOC_X");
		return location;
	}

	public int getLocY(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_LOC_Y FROM cs2.ENTITY where E_ID=" + id
						+ ";");
		rs.next();
		int location = rs.getInt("E_LOC_Y");
		return location;
	}

	public void setWeapon(int id, int online) {
		String sql = "UPDATE cs2.ENTITY set E_WEP =" + online
				+ " where E_ID = " + id + ";";
		queue.enqueue(sql);
	}

	public int getWeapon(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_WEP FROM cs2.ENTITY where E_ID=" + id
						+ ";");
		rs.next();
		int online = rs.getInt("E_WEP");
		return online;
	}

	public void setHat(int id, int online) {
		String sql = "UPDATE cs2.ENTITY set E_HAT =" + online
				+ " where E_ID = " + id + ";";
		queue.enqueue(sql);
	}

	public int getHat(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_HAT FROM cs2.ENTITY where E_ID=" + id
						+ ";");
		rs.next();
		int online = rs.getInt("E_HAT");
		return online;
	}

	public void setExp(int id, int online) {
		String sql = "UPDATE cs2.ENTITY set E_EXP =" + online
				+ " where E_ID = " + id + ";";
		queue.enqueue(sql);
	}

	public int getExp(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_EXP FROM cs2.ENTITY where E_ID=" + id
						+ ";");
		rs.next();
		int online = rs.getInt("E_EXP");
		return online;
	}

	public void setAcc(int id, int online) {
		String sql = "UPDATE cs2.ENTITY set E_ACC =" + online
				+ " where E_ID = " + id + ";";
		queue.enqueue(sql);
	}

	public int getAcc(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_ACC FROM cs2.ENTITY where E_ID=" + id
						+ ";");
		rs.next();
		int online = rs.getInt("E_ACC");
		return online;
	}

	public void setIp(Connection connection, int id, String ip) {
		String sql = "UPDATE cs2.ENTITY set E_IP =" + ip + " where E_ID = "
				+ id + ";";
		queue.enqueue(sql);
	}

	public String getIp(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_IP FROM cs2.ENTITY where E_ID=" + id
						+ ";");
		rs.next();
		String online = rs.getString("E_IP");
		return online;
	}

	public void setHP(int id, int online) {
		String sql = "UPDATE cs2.ENTITY set E_HP =" + online + " where E_ID = "
				+ id + ";";
		queue.enqueue(sql);
	}

	public int getHP(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_HP FROM cs2.ENTITY where E_ID=" + id
						+ ";");
		rs.next();
		int online = rs.getInt("E_HP");
		return online;
	}

	public void setMP(int id, int online) {
		String sql = "UPDATE cs2.ENTITY set E_MP =" + online + " where E_ID = "
				+ id + ";";
		queue.enqueue(sql);
	}

	public int getMP(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_MP FROM cs2.ENTITY where E_ID=" + id
						+ ";");
		rs.next();
		int online = rs.getInt("E_MP");
		return online;
	}

	public void setMPPot(int id, int online) {
		String sql = "UPDATE cs2.ENTITY set E_MP_POTS =" + online
				+ " where E_ID = " + id + ";";
		queue.enqueue(sql);
	}

	public int getMPPot(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_MP_POTS FROM cs2.ENTITY where E_ID="
						+ id + ";");
		rs.next();
		int online = rs.getInt("E_MP_POTS");
		return online;
	}

	public void setHPPot(int id, int online) {
		String sql = "UPDATE cs2.ENTITY set E_HP_POTS =" + online
				+ " where E_ID = " + id + ";";
		queue.enqueue(sql);
	}

	public int getHPPot(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_HP_POTS FROM cs2.ENTITY where E_ID="
						+ id + ";");
		rs.next();
		int online = rs.getInt("E_HP_POTS");
		return online;
	}

	public void setClass(int id, int online) {
		String sql = "UPDATE cs2.ENTITY set E_CLASS =" + online
				+ " where E_ID = " + id + ";";
		queue.enqueue(sql);
	}

	public int getClass(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_CLASS FROM cs2.ENTITY where E_ID=" + id
						+ ";");
		rs.next();
		int online = rs.getInt("E_CLASS");
		return online;
	}

	public void setHPMax(int id, int online) {
		String sql = "UPDATE cs2.ENTITY set E_HP_MAX =" + online
				+ " where E_ID = " + id + ";";
		queue.enqueue(sql);
	}

	public int getHPMax(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_HP_MAX FROM cs2.ENTITY where E_ID="
						+ id + ";");
		rs.next();
		int online = rs.getInt("E_HP_MAX");
		return online;
	}

	public void setMPMax(int id, int online) {
		String sql = "UPDATE cs2.ENTITY set E_MP_MAX =" + online
				+ " where E_ID = " + id + ";";
		queue.enqueue(sql);
	}

	public int getMPMax(Connection connection, int id) throws Exception {
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("SELECT E_MP_MAX FROM cs2.ENTITY where E_ID="
						+ id + ";");
		rs.next();
		int online = rs.getInt("E_MP_MAX");
		return online;
	}
	
	public static void main(String[] args) {
		Connection connection = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String serverName = "172.16.5.1";
			String mydatabase = "cs2";
			String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a
			// JDBC
			// url
			String username = "cs2";
			String password = "cs2";
			SQLDatabase a = new SQLDatabase();
			
		} catch (Exception e) {
			// Could not find the database driver
			System.out.println(e);
		}

	}
}